package com.codefactory.generator.utils;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelReader {

    public static List<List<Object>> readExcel(String filePath, int sheetIndex) {
        List<List<Object>> data = new ArrayList<>();

        try {
            FileInputStream file = new FileInputStream(filePath);

            // 创建一个工作簿对象
            Workbook workbook = WorkbookFactory.create(file);

            // 获取指定索引的工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);

            // 创建公式求值器
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            // 迭代行
            for (Row row : sheet) {
                List<Object> rowData = new ArrayList<>();

                // 迭代列
                for (Cell cell : row) {
                    Object cellValue;

                    // 根据单元格类型读取内容并存入列表
                    switch (cell.getCellType()) {
                        case STRING:
                            cellValue = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            cellValue = cell.getNumericCellValue();
                            break;
                        case BOOLEAN:
                            cellValue = cell.getBooleanCellValue();
                            break;
                        case FORMULA:
                            // 获取公式内容
                            cellValue = ExcelReader.evaluateFormulaCell(evaluator, cell);
                            break;
                        case BLANK:
                            cellValue = ""; // 或者可以存入 null，视情况而定
                            break;
                        default:
                            cellValue = null;
                    }

                    rowData.add(cellValue);
                }

                data.add(rowData);
            }

            workbook.close(); // 关闭工作簿

        } catch (Exception e) {
            e.printStackTrace();
        }

        return data;
    }

    // 方法用于计算公式单元格的值
    private static Object evaluateFormulaCell(FormulaEvaluator evaluator, Cell cell) {
        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
            case STRING:
                return cellValue.getStringValue();
            case NUMERIC:
                return cellValue.getNumberValue();
            case BOOLEAN:
                return cellValue.getBooleanValue();
            case ERROR:
                return FormulaError.forInt(cellValue.getErrorValue()).getString();
            default:
                return null;
        }
    }

    public static void main(String[] args) {
        String filePath = "path/to/your/excel/file.xlsx";
        int sheetIndex = 0; // 指定要读取的工作表索引

        List<List<Object>> excelData = ExcelReader.readExcel(filePath, sheetIndex);

        // 输出读取的数据
        for (List<Object> rowData : excelData) {
            for (Object cellData : rowData) {
                System.out.print(cellData + "\t");
            }
            System.out.println(); // 换行
        }
    }
}
